Solution set.

Due date: Apr 28

Files to be submitted:
  Hw4.zip

Purpose: To gain experience coding in Javascript, writing web services, and using PHP's composer. To practice extending someone else's code.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

LO2 -- Write schemas, DTDs, and style sheets for XML documents.

LO4 -- Write client-side scripts that validate HTML forms.

LO5 -- Develop and deploy web applications that involve components, web services, and database

Specification:

For this homework, you will make an application called "Web Sheets" that lets users create and share spreadsheets on the web. What follows is some Javascript starter code for drawing and maintaining a spreadsheet:

/**
 * Defines a class for drawing and editing spreadsheets within a tag with
 *
 * Example uses:
 * spreadsheet = new Spreadsheet(some_html_element_id, 
 *     [["Tom",5],["Sally", 6]]); //read-only
 * spreadsheet.draw();
 *
 * spreadsheet2 = new Spreadsheet(some_html_element_id2, 
 *     [["Tom",5],["Sally", 6]], {"mode":"write"}); //editable
 * spreadsheet2.draw();
 *
 * @param String spreadsheet_id the id of the tag in which to draw the
 *      spreadsheet
 * @param Array supplied_data two dimensional array of the rows and columns
 *      of data for the spreadsheet
 */
function Spreadsheet(spreadsheet_id, supplied_data)
{
    var self = this;
    var p = Spreadsheet.prototype;
    var properties = (typeof arguments[2] !== 'undefined') ?
        arguments[2] : {};
    var container = document.getElementById(spreadsheet_id);
    if (!container) {
        return false;
    }
    supplied_data = eval(supplied_data);
    if (!Array.isArray(supplied_data)) {
        supplied_data = [];
    }
    var width = 0;
    for (index in supplied_data) {
        if (!Array.isArray(supplied_data[index])) {
            supplied_data[index] = [];
        }
        if ([index].length > width) {
            width = supplied_data[index].length;
        }
    }
    var length = supplied_data.length;
    var data = [];
    for (var i = 0; i < length; i++) {
        data[i] = [];
        for (var j = 0; j < width; j++) {
            data[i][j] = (typeof supplied_data[i][j] == 'undefined') ? "" :
                supplied_data[i][j];
        }
    }
    var property_defaults = {
        'mode' : 'read', // currently, only supports csv
        'data_id' : spreadsheet_id + "-data",
        'data_name' : 'page',
        'table_style' : 'overflow:auto;height:6in;',
    };
    for (var property_key in property_defaults) {
        if (typeof properties[property_key] !== 'undefined') {
            this[property_key] = properties[property_key];
        } else {
            this[property_key] = property_defaults[property_key];
        }
    }
    /**
     * Main function used to draw the spreadsheet with the container tag
     */
    p.draw = function()
    {
        //used to draw a csv based on spreadsheet data
        var table = "<div style='" + self.table_style + "'>";
        var length = data.length;
        var width = data[0].length;
        var add_button = "";
        var delete_button = "";
        var pre_delete_button = "";
        if (self.mode == 'write') {
            table += "<input id='" + self.data_id+ "' type='hidden' " +
                "name='" + self.data_name + "' value='" + JSON.stringify(
                data)+ "' />";
            add_button = "<button>+</button>";
            pre_delete_button = "<button>-</button>";
        }
        table += "<table border='1' ><tr><th></th>";
        for (var i = 0; i < width; i++) {
            table += "<th style='min-width:1in;text-align:right;'>" +
                delete_button + self.letterRepresentation(i) + add_button +
                "</th>";
            delete_button = pre_delete_button;
        }
        table += "</tr>";
        delete_button = "";
        for (i = 0; i < length; i++) {
            table +="<tr><th style='min-width:1.1in;text-align:right;'>" +
                delete_button + (i + 1) + add_button + "</th>";
            delete_button = pre_delete_button;
            for (var j = 0; j < width; j++) {
                var item = "";
                if (typeof data[i][j] == 'string') {
                    item = data[i][j];
                    if (item.charAt(0) == '=') {
                        item = self.evaluateCell(item.substring(1), 0)[1];
                    }
                }
                table += "<td>" + item + "</td>";
            }
            table += "</tr>";
        }
        table += "</table></div>";
        container.innerHTML = table;
    }
    /**
     * Calculates the value of a cell expression in a spreadsheet. Currently,
     * a cell expression is either an integer literal, a non-scientific notation
     * float litera, a cell name literal, or of the form
     * (cell_exp1 op cell_exp2) where cell_exp1 and cell_exp2 are cell
     * expressions that don't evaluate to strings and op is one of +, -, *, /
     * whitespace is ignore in cell expressions
     *
     * @param String cell_expression a string representing a formula to
     * calculate from a spreadsheet file
     * @param Number location character position in cell_expression to start
     *      evaluating from
     * @return mixed the value of the cell or the String 'NaN' if the expression
     *    was not evaluatable
     */
    p.evaluateCell = function(cell_expression, location)
    {
        var out = [location, false];
        if (location >= cell_expression.length) {
            return out;
        }
        location = self.skipWhitespace(cell_expression, location);
        out[0] = location;
        if(cell_expression.charAt(location) == "(") {
            left_out = self.evaluateCell(cell_expression, location + 1);
            if (!['+', '-', '*', '/'].includes(
                cell_expression.charAt(left_out[0])) ||
                typeof left_out[1] == 'String') {
                out[0] = left_out[0];
                out[1] = "NaN";
                return out;
            }
            right_out = self.evaluateCell(cell_expression, left_out[0] + 1);
            if (cell_expression.charAt(right_out[0]) != ')' ||
                typeof right_out[1] == 'String') {
                out[0] = right_out[0];
                out[1] = "NaN";
                return out;
            }
            out[0] = self.skipWhitespace(cell_expression, right_out[0] + 1);
            out[1] = eval("" + left_out[1] +
                cell_expression.charAt(left_out[0]) + right_out[1]);
            return out;
        } else if (cell_expression.charAt(location) == "-") {
            sub_out = self.evaluateCell(cell_expression, location + 1);
            if (sub_out[1] == 'NaN') {
                return sub_out;
            }
            out[0] = self.skipWhitespace(cell_expression, sub_out[0]);
            out[1] = - sub_out[1];
            return out;
        }
        var rest = cell_expression.substring(location);
        var value = rest.match(/^\-?\d+(\.\d*)?|^\-?\.\d+/);
        if (value !== null) {
            out[0] = self.skipWhitespace(cell_expression,location +
                value[0].length +1);
            out[1] = (value[0].match(/\./) == '.') ? parseFloat(value[0]) :
                parseInt(value[0]);
            return out;
        }
        value = rest.match(/^[A-Z]+\d+/);
        if (value !== null) {
            out[0] = self.skipWhitespace(cell_expression,location +
                value.length + 1);
            var row_col = self.cellNameAsRowColumn(value.toString().trim());
            out[1] = data[row_col[0] - 1][row_col[1]];
        }
        return out;
    }
    /**
     * Returns the position of the first non-whitespace character after
     * location in the string (returns location if location is non-WS or
     * if no location found).
     *
     * @param String haystack string to search in
     * @param Number location where to start search from
     * @return Number position of non-WS character
     */
    p.skipWhitespace = function(haystack, location)
    {
        var next_loc = haystack.substring(location).search(/\S/);
        if (next_loc > 0) {
            location += next_loc;
        }
        return location;
    }
    /**
     * Converts a decimal number to a base 26 number string using A-Z for 0-25.
     * Used where drawing column headers for spreadsheet
     * @param Number number the value to convert to base 26
     * @return String result of conversion
     */
    p.letterRepresentation = function(number)
    {
        var pre_letter;
        var out = "";
        do {
            pre_letter = number % 26;
            number = Math.floor(number/26);
            out += String.fromCharCode(65 + pre_letter);
        } while (number > 25);
        return out;
    }
    /**
     * Given a cell name string, such as B4, converts it to an ordered pair
     * suitable for lookup in the spreadsheets data array. On B4,
     * [3, 1] would be returned.
     *
     * @param String cell_name name to convert
     * @return Array ordered pair corresponding to name
     */
    p.cellNameAsRowColumn = function(cell_name)
    {
        var cell_parts = cell_name.match(/^([A-Z]+)(\d+)$/);
        if (cell_parts == null) {
            return null;
        }
        var column_string = cell_parts[1];
        var len = column_string.length;
        var column = 0;
        var shift = 1;
        for (var i = 0; i < len; i++) {
            column += (column_string.charCodeAt(i) - 65) * shift;
            shift = 26;
        }
        return [parseInt(cell_parts[2]), column];
    }
    /**
     * Callback for click events on spreadsheet. Determines if the event
     * occurred on a spreadsheet cell. If so, it opens a prompt for a
     * new value for the cell and updates the cell and the associated form
     * hidden input value.
     * @param Object event click event object
     */
    p.updateCell = function (event) {
        var type = (event.target.innerHTML == "+") ? 'add' :
            (event.target.innerHTML == "-") ? 'delete' :'cell';
        var target = (type == 'cell') ? event.target :
            event.target.parentElement;
        var row = target.parentElement.rowIndex - 1;
        var column = target.cellIndex - 1;
        var length = data.length;
        var width = data[0].length;
        if (row >= 0 && column >= 0) {
            var new_value = prompt(self.letterRepresentation(column) +
                (row + 1), data[row][column]);
            if (new_value != null) {
                data[row][column] = new_value;
                data_elt = document.getElementById(self.data_id);
                data_elt.value = JSON.stringify(data);
                event.target.innerHTML = new_value;
            }
        } else if (type == 'add' && row == -1 && column >= 0) {
            for (var i = 0; i < length; i++) {
                for (var j = width; j > column + 1; j--) {
                    data[i][j] = data[i][j-1];
                }
                data[i][column + 1] = "";
            }
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        } else if (type == 'add' && row >= 0 && column == -1) {
            data[length] = [];
            for (var i = length; i > row + 1; i--) {
                for (var j = 0; j < width; j++) {
                    data[i][j] = data[i - 1][j];
                }
            }
            for (var j = 0; j < width; j++) {
                data[row + 1][j] = "";
            }
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        } else if (type == 'delete' && row == -1 && column >= 0) {
            for (var i = 0; i < length; i++) {
                for (var j = column ; j < width - 1; j++) {
                    data[i][j] = data[i][j + 1];
                }
                data[i].pop();
            }
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        } else if (type == 'delete' && row >= 0 && column == -1) {
            for (var i = row; i < length - 1; i++) {
                    data[i] = data[i + 1];
            }
            data.pop();
            data_elt = document.getElementById(self.data_id);
            data_elt.value = JSON.stringify(data);
            self.draw();
        }
        event.stopPropagation();
        event.preventDefault();
    }
    if (this.mode == 'write') {
        container.addEventListener("click", self.updateCell, true);
    }
}

Here are some mock-ups of what pages on your site should look like:

Here are the requirements that your project should meet:

1. Your Hw4.zip folder has a readme.txt file with all the names and ids for your group. Any configurations changes that the graders needs to do to get your project running on the grader's system should be settable in a file Hw4/src/configs/Config.php.
2. Your project makes use of git. If the grader does a git log in your homework folder, and then does a git diff between successive commits. The difference should always be less than 150 lines.
3. Your project has been broken up into issues as part of the development process. There should be a folder in your Hw4 folder, called issues, in which you should have a one text file/issue. The issue files's filenames should follow the format: number_short_title_for_issue.txt. For example, 0001_Set_up_folder_structure.txt. In the text file there should be a title of the issue, who in your group its assigned to, and a short description of what needed to be done for the issue. This can be followed by any discussion back-and-forth on the issue.
4. All the web pages you output validate as XHTML 5. Your project is written using namespaces. You only create variables, arrays, objects, define new classes, etc. in the namespace cool_name_for_your_group\hw4 and subnamespaces thereof.
5. The folder structure used by your project and the format for urls should be the same as required in the Hw3 description. V_URL in the mock up is:

   BASE_URL?index.php?c=main&m=view
   

6. The backend of your project is required to be developed in PHP using composer. You should have a composer.json file which requires monolog/monolog
7. The only require or include statement your app itself is allowed to have should occur in the index.php file and should be a require of the Composer generated autoload.php file.
8. In addition to the top level src folder for your project, you can have a top level folder app_data. You should use Monolog to write a log entry to the file spread.log in this folder each time a user visits the Landing page, an Edit Sheet Page, or a Read Sheet page. You can log additional things as you like.
9. On each of the three kinds of mock up pages the Web Sheets link should take one back to the landing page.
10. On the Landing page, name and hash codes should not involve non-(alphanumeric or space) characters. If Go is clicked, and this field has such characters, you should use Javascript to give a warning and not submit the form.
11. Spreadsheets should be stored as JSON encoded arrays in a MySQL database in the backend. This database should have all its tables in BCNF. You should have at a minimum two tables: SHEET(sheet_id, sheet_name, sheet_data) and SHEET_CODES(sheet_id, hash_code, code_type).
12. If a user enters a name, or a hash_code of type "edit" on the landing page, the user should be taken to the Edit Sheet Page. A new spreadsheet should by default have at least two rows and two columns. (The + and - buttons on a spreedsheet can change this). If the hash_code is for an existing spreadsheet it should be drawn and editable.
13. spreadsheet.js should be modified so that editing a field in spreadsheet sends an XmlHttpRequest back to your app to update the field in the database. Data should be sent as JSON, and the controller called should be ApiController (use c=api in the url). This should be testable on the Edit Sheet Page.
14. If on the landing page a user enters a hash_code of type "read", they should be taken to the Read Sheet Page and the spreadsheet corresponding to that code should be displayed.
15. The root Hw4 project folder should contain a spreadsheet.dtd file defining an XML language suitable for storing spreadsheets of the type used in this project.
16. If on the landing page, a user enters a hash_code of type "file", the spreadsheet should be displayed as an XML file that conforms to the edit given by spreadsheet.dtd. The grader will verify this using oXygen.
17. If one enters the Edit Url, Read Url, or File Url's directly in the browser url bar one should be taken to the correct page with respect to the current spreadsheet.
18. spreadsheet.js should be modified so that when a using clicks on an editable cell rather than a prompt being used to get a new value the cell itself should be highlighted and a value can be entered in it (more like a traditional spreadsheet like Excel).
19. Currently, cells with expressions like =A1 or =((A1 + B1) * C3) will be evaluated and the value will be drawn. spreadsheet.js should be extended to accept expressions like =avg(A1:A10) which would compute the average value of A1, A2, ...., A10.

Point Breakdown